package com.huaxia.dao.excellent;

import com.huaxia.pojo.excellent.BranchQuarterFHS;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/10/21 9:42
 */

public interface BranchQuarterFHSMapper {

    @Select("select g1.branchsname,g1.branchNum,g2.branch10000,g2.branch6000,round(decode(g1.branchNum,0,-9999,(g2.branch10000+g2.branch6000)*100/g1.branchNum),1)branchFHSYield from(\n" +
            "\n" +
            "select t1.branchsname,NVL(t2.branchNum,0)branchNum from SD_BRANCH t1 left join (\n" +
            "select SUBSTR(t.MANAGECOM,1,6)branchcode,count(t.MANAGECOM) branchNum from LAFHAGENT t where t.FHAGENTGRADE !='FH00' group by SUBSTR(t.MANAGECOM,1,6))t2 on t1.branchcode=t2.branchcode\n" +
            ")g1,(\n" +
            "select w1.branchsname,NVL(w2.branch10000,0)branch10000,NVL(w2.branch6000,0)branch6000 from SD_BRANCH w1 left join(\n" +
            "select SUBSTR(n.MANAGECOM,1,6)branchcode,count(case when n.q1>=10000 then 1 end) branch10000,count(case when n.q1>=6000 then 1 end) branch6000 from(\n" +
            "select h1.agentcode,h1.q1,h2.managecom from\n" +
            "(\n" +
            "select t1.agentcode,t1.cb-NVL(t2.yt,0)q1 from\n" +
            "(select t.agentcode, SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') AND RNFLAG='N' group by t.agentcode\n" +
            ")t1 left join\n" +
            "(select  t.agentcode, SUM(t.WRITTENSTADPREM)Yt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' GROUP BY t.agentcode\n" +
            ")t2 on t1.agentcode=t2.agentcode\n" +
            ")h1,(\n" +
            "select t.agentcode, t.managecom from LAFHAGENT t where t.FHAGENTGRADE!='FH00'\n" +
            ")h2 where h1.agentcode=h2.agentcode  )n group by SUBSTR(n.MANAGECOM,1,6)) w2 on w1.branchcode=w2.branchcode)g2 where g1.branchsname=g2.branchsname order by branchFHSYield desc\n")
    List<BranchQuarterFHS> getBranchQuarterFHS1();

    @Select("select g1.branchsname,g1.branchNum,g2.branch10000,g2.branch6000,round(decode(g1.branchNum,0,-9999,(g2.branch10000+g2.branch6000)*100/g1.branchNum),1)branchFHSYield from(\n" +
            "\n" +
            "select t1.branchsname,NVL(t2.branchNum,0)branchNum from SD_BRANCH t1 left join (\n" +
            "select SUBSTR(t.MANAGECOM,1,6)branchcode,count(t.MANAGECOM) branchNum from LAFHAGENT t where t.FHAGENTGRADE !='FH00' group by SUBSTR(t.MANAGECOM,1,6))t2 on t1.branchcode=t2.branchcode\n" +
            ")g1,(\n" +
            "select w1.branchsname,NVL(w2.branch10000,0)branch10000,NVL(w2.branch6000,0)branch6000 from SD_BRANCH w1 left join(\n" +
            "select SUBSTR(n.MANAGECOM,1,6)branchcode,count(case when n.q1>=10000 and n.q2>=10000 then 1 end) branch10000,count(case when n.q1>=6000 and n.q2>=6000 then 1 end) branch6000 from(\n" +
            "select h1.agentcode,h1.q1,h2.q2,h3.managecom from\n" +
            "(\n" +
            "select t1.agentcode,t1.cb-NVL(t2.yt,0)q1 from\n" +
            "(select t.agentcode, SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') AND RNFLAG='N' group by t.agentcode\n" +
            ")t1 left join\n" +
            "(select  t.agentcode, SUM(t.WRITTENSTADPREM)Yt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' GROUP BY t.agentcode\n" +
            ")t2 on t1.agentcode=t2.agentcode\n" +
            ")h1,(\n" +
            "select t1.agentcode,t1.cb-NVL(t2.yt,0)q2 from\n" +
            "(select t.agentcode, SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),-1),'yyyy/mm') AND RNFLAG='N' group by t.agentcode\n" +
            ")t1 left join\n" +
            "(select  t.agentcode, SUM(t.WRITTENSTADPREM)Yt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),-1),'yyyy/mm')  AND RNFLAG='N' GROUP BY t.agentcode\n" +
            ")t2 on t1.agentcode=t2.agentcode\n" +
            ")h2,(\n" +
            "select t.agentcode, t.managecom from LAFHAGENT t where t.FHAGENTGRADE!='FH00'\n" +
            ")h3 where h1.agentcode=h2.agentcode and h2.agentcode=h3.agentcode  )n group by SUBSTR(n.MANAGECOM,1,6)) w2 on w1.branchcode=w2.branchcode)g2 where g1.branchsname=g2.branchsname order by branchFHSYield desc\n")
    List<BranchQuarterFHS> getBranchQuarterFHS2();

    @Select("select g1.branchsname,g1.branchNum,g2.branch10000,g2.branch6000,round(decode(g1.branchNum,0,-9999,(g2.branch10000+g2.branch6000)*100/g1.branchNum),1)branchFHSYield from(\n" +
            "\n" +
            "select t1.branchsname,NVL(t2.branchNum,0)branchNum from SD_BRANCH t1 left join (\n" +
            "select SUBSTR(t.MANAGECOM,1,6)branchcode,count(t.MANAGECOM) branchNum from LAFHAGENT t where t.FHAGENTGRADE !='FH00' group by SUBSTR(t.MANAGECOM,1,6))t2 on t1.branchcode=t2.branchcode\n" +
            ")g1,(\n" +
            "select w1.branchsname,NVL(w2.branch10000,0)branch10000,NVL(w2.branch6000,0)branch6000 from SD_BRANCH w1 left join(\n" +
            "select SUBSTR(n.MANAGECOM,1,6)branchcode,count(case when n.q1>=10000 and n.q2>=10000 and n.q3>=10000 then 1 end) branch10000,count(case when n.q1>=6000 and n.q2>=6000 and n.q3>=10000 then 1 end) branch6000 from(\n" +
            "select h1.agentcode,h1.q1,h2.q2,h3.q3,h4.managecom from\n" +
            "(\n" +
            "select t1.agentcode,t1.cb-NVL(t2.yt,0)q1 from\n" +
            "(select t.agentcode, SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') AND RNFLAG='N' group by t.agentcode\n" +
            ")t1 left join\n" +
            "(select  t.agentcode, SUM(t.WRITTENSTADPREM)Yt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' GROUP BY t.agentcode\n" +
            ")t2 on t1.agentcode=t2.agentcode\n" +
            ")h1,(\n" +
            "select t1.agentcode,t1.cb-NVL(t2.yt,0)q2 from\n" +
            "(select t.agentcode, SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),-1),'yyyy/mm') AND RNFLAG='N' group by t.agentcode\n" +
            ")t1 left join\n" +
            "(select  t.agentcode, SUM(t.WRITTENSTADPREM)Yt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),-1),'yyyy/mm')  AND RNFLAG='N' GROUP BY t.agentcode\n" +
            ")t2 on t1.agentcode=t2.agentcode\n" +
            ")h2,(\n" +
            "select t1.agentcode,t1.cb-NVL(t2.yt,0)q3 from\n" +
            "(select t.agentcode, SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),-2),'yyyy/mm') AND RNFLAG='N' group by t.agentcode\n" +
            ")t1 left join\n" +
            "(select  t.agentcode, SUM(t.WRITTENSTADPREM)Yt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),-2),'yyyy/mm')  AND RNFLAG='N' GROUP BY t.agentcode\n" +
            ")t2 on t1.agentcode=t2.agentcode\n" +
            ")h3,(\n" +
            "select t.agentcode, t.managecom from LAFHAGENT t where t.FHAGENTGRADE!='FH00'\n" +
            ")h4 where h1.agentcode=h2.agentcode and h2.agentcode=h3.agentcode and h3.agentcode=h4.agentcode )n group by SUBSTR(n.MANAGECOM,1,6)) w2 on w1.branchcode=w2.branchcode)g2 where g1.branchsname=g2.branchsname order by branchFHSYield desc")
    List<BranchQuarterFHS> getBranchQuarterFHS3();
}
